package com.eduction.modules.system.dao;

import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.eduction.common.dbutil.DbHelper;
import com.eduction.modules.system.entity.SchoolItemData;

@Repository
public class SchoolItemDataRepository {

	@Autowired
	private DbHelper dbHelper;
	@Autowired
	private SchoolItemDataDao schoolItemDataDao;
	
	/**
	 * 查询分项值
	 * @param schoolId
	 * @param itemId
	 * @return
	 * @throws SQLException 
	 */
	public SchoolItemData querySchoolItemData(String schoolId, String itemId,int year) throws SQLException{
//		String sql = "SELECT a.* FROM t_school_item_data as a  "
//				+ " where  a.s_school_id = ? and a.s_item_id = ? and a.s_year=? ";
//		return dbHelper.getBean(sql, SchoolItemData.class, schoolId,itemId,year);
		String sql = "SELECT a.* FROM t_school_item_data as a  "
				+ " where  a.s_school_id = ? and a.s_item_id = ? ";
		return dbHelper.getBean(sql, SchoolItemData.class, schoolId,itemId);
	}
	
	/**
	 * b保存分项值
	 */
	public void saveSchoolItemData(SchoolItemData s){
		schoolItemDataDao.save(s);
	}
	
	public void updateSchoolItemData(SchoolItemData s) throws SQLException{
//		String sql = " update t_school_item_data set s_value=?  where  s_school_id = ? and s_item_id = ? and s_year = ? ";
//		dbHelper.execute(sql, new Object[] {s.getS_value(),s.getS_school_id(),s.getS_item_id(),s.getS_year()});
		String sql = " update t_school_item_data set s_value=?  where  s_school_id = ? and s_item_id = ? ";
		dbHelper.execute(sql, new Object[] {s.getS_value(),s.getS_school_id(),s.getS_item_id()});
	}
	
	/**
	 * 根据学校id查看所有分项
	 * @param schoolId
	 * @return
	 * @throws SQLException
	 */
    public List<SchoolItemData> querySchoolItemDataBySchoolId(String itemId,String schoolId) throws SQLException {
		String sql = "SELECT a.*,i.s_name FROM t_school_item_data as a LEFT JOIN t_observation_point o on a.s_item_id=o.p_id LEFT JOIN t_detailed d ON o.p_detailed_id " +
				"=d.p_id LEFT JOIN t_son_target s ON s.p_id=d.p_target_id LEFT JOIN t_index_target i on i.p_id=s.p_index_id WHERE i.p_id=? AND a.s_school_id=?";
		return dbHelper.getBeanList(sql, SchoolItemData.class,itemId, schoolId);
    }

    public List<SchoolItemData> querySchoolItemDataByItemIdAndSchoolId(String itemId, String schoolId,String year) throws SQLException{
		String sql = "SELECT a.* FROM t_school_item_data as a LEFT JOIN t_observation_point o on a.s_item_id=o.p_id  WHERE o.p_id=? AND a.s_school_id=? AND s_year= ?";
		return dbHelper.getBeanList(sql, SchoolItemData.class,itemId, schoolId,year);
    }
}
